﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using CYZHAPI.Entity;
using Oracle.ManagedDataAccess.Client;

namespace CYZHAPI.Common.DB {
    public abstract class ReponsitoryBase<T> : IRepository<T> where T : class {
        private const decimal MAX_VALUE = 9999999999999999999;
        public decimal maxXh {　　
            get { return MAX_VALUE; }
        }
        private OracleConnection m_Connection = null;
        public ReponsitoryBase (IDapperContext dbContext) {
            m_Connection = DBConnSingleton.GetInstance (dbContext.GetConnectionString ());
        }

        public bool delete (long key, string keyColumn, string tableName) {
            string sSql = "DELETE FROM " + tableName + " WHERE " + keyColumn + "=" + Convert.ToString (key);
            OracleConnection objConn = this.GetReadingConnection ();
            using (OracleCommand cmd = objConn.CreateCommand ()) {
                try {
                    cmd.CommandText = sSql;
                    cmd.ExecuteNonQuery ();
                } catch (Exception e) {
                    Console.WriteLine (e.Message);
                    return false;
                }
            }
            return true;
        }

        public DataSet GetList (string tableName, string sWhere) {
            DataSet dtTmp = new DataSet ();
            string sSql = "select * from " + tableName + " where " + sWhere;
            OracleConnection objConn = this.GetReadingConnection ();
            using (OracleCommand cmd = objConn.CreateCommand ()) {
                try {
                    cmd.CommandText = sSql;
                    //cmd.BindByName = true;
                    using (OracleDataAdapter dataAdapter = new OracleDataAdapter ()) {
                        dataAdapter.SelectCommand = cmd; //检索command设置
                        dataAdapter.Fill (dtTmp); //检索结果保存在dtTmp数据集中

                    }
                } catch (Exception e) {
                    Console.WriteLine (e.Message);
                    return null;
                }
            }
            return dtTmp;
        }

        public OracleConnection GetReadingConnection () {
            return m_Connection;
        }

        public decimal GetProblemXH (string sequencesName) {
            decimal maxXh = MAX_VALUE;
            string strSeqIDQuerySQL = "select " + sequencesName + ".nextval from dual";
            OracleConnection objConn = this.GetReadingConnection ();
            using (OracleCommand cmd = objConn.CreateCommand ()) {
                try {
                    cmd.CommandText = strSeqIDQuerySQL;
                    OracleDataReader dateReader = cmd.ExecuteReader ();
                    while (dateReader.Read ()) {
                        maxXh = dateReader.GetDecimal (0);
                    }

                } catch (Exception e) {
                    Console.WriteLine (e.Message);
                    return maxXh;
                }
            }
            return maxXh;
        }
        public bool insert (string TableName, DataSet itemModel) {
            OracleConnection objConn = this.GetReadingConnection ();
            //string sSql = "select a.userid,b.code, b.name,b.pcode,b.cmd from USER_MAP_ROL a,ALGORITHM_CODE b where a.rolid=b.code and a.userid=:userid and pcode=:pcode";
            using (OracleCommand cmd = objConn.CreateCommand ()) {
                try {
                    string sSql = ""; //INSERT INTO 表名称 VALUES (值1, 值2,....)
                    string sColumns = "";
                    string sColumnsV = "";
                    foreach (DataColumn v in itemModel.Tables[0].Columns) {
                        if (sColumns.Equals ("")) {
                            sColumns += v.ColumnName;
                            sColumnsV = ":" + v.ColumnName;
                        } else {
                            sColumns += "," + v.ColumnName;
                            sColumnsV += "," + ":" + v.ColumnName;
                        }
                        //cmd.Parameters.Add(":"+v.columnsName, v.columnsType, v.columnsSize, v.columnsName);
                    }
                    sSql = "insert into " + TableName + "(" + sColumns + ") VALUES (" + sColumnsV + ")";
                    cmd.CommandText = sSql;
                    foreach (DataRow dr in itemModel.Tables[0].Rows) {
                        cmd.Parameters.Clear ();
                        OracleParameter[] oracleParams = new OracleParameter[itemModel.Tables[0].Columns.Count];
                        int oracleParam_number = 0;
                        foreach (DataColumn dc in itemModel.Tables[0].Columns) {
                            oracleParams[oracleParam_number] = new OracleParameter (dc.ColumnName, dr[dc]);
                            oracleParam_number++;
                        }
                        cmd.Parameters.AddRange (oracleParams);
                        cmd.ExecuteNonQuery ();
                    }
                } catch (Exception e) {
                    Console.WriteLine (e.Message);
                    return false;
                }
            }
            return true;
        }

        public bool update (string keyColumn, string tableName, DataSet itemModel) {
            OracleConnection objConn = this.GetReadingConnection ();
            using (OracleCommand cmd = objConn.CreateCommand ()) {
                try {
                    string sSql = ""; //INSERT INTO 表名称 VALUES (值1, 值2,....)
                    string sColumns = "";
                    foreach (DataColumn v in itemModel.Tables[0].Columns) {
                        if (keyColumn.Equals (v.ColumnName)) continue;
                        if (sColumns.Equals ("")) {
                            sColumns += v + "=:" + v;
                        } else {
                            sColumns += "," + v + "=:" + v;
                        }
                        //cmd.Parameters.Add(":"+v.columnsName, v.columnsType, v.columnsSize, v.columnsName);
                    }

                    foreach (DataRow dr in itemModel.Tables[0].Rows) {
                        cmd.Parameters.Clear ();
                        OracleParameter[] oracleParams = new OracleParameter[itemModel.Tables[0].Columns.Count-1];
                        int oracleParam_number = 0;
                        string keyValue="";
                        foreach (DataColumn dc in itemModel.Tables[0].Columns) {
                            if(dc.ColumnName.Equals(keyColumn)){
                                //OracleType
                                //oracleParams[oracleParam_number] = new OracleParameter (":"+dc.ColumnName,OracleDbType.Long,10);
                                //oracleParams[oracleParam_number].Value = dr[dc];
                                keyValue = Convert.ToString(dr[dc]);
                                //oracleParam_number++;
                                continue;
                            }
                            oracleParams[oracleParam_number] = new OracleParameter (":"+dc.ColumnName, dr[dc]);
                            oracleParam_number++;
                        }
                        //oracleParams[++oracleParam_number] = new OracleParameter(dc, sPropertiesValue[getListValue(dc, sPropertiesKey)]);
                        sSql = "UPDATE " + tableName + " SET " + sColumns + " where " + keyColumn + "=" + keyValue; // + keyColumu;
                        cmd.CommandText = sSql;
                        cmd.Parameters.AddRange (oracleParams);
                        cmd.ExecuteNonQuery ();
                    }
                } catch (Exception e) {
                    Console.WriteLine (e.Message);
                    return false;
                }
            }
            return true;
        }

        private int getListValue (string key, ArrayList sPropertiesKey) {
            int i = 0;
            foreach (string v in sPropertiesKey) {
                if (key == v) {
                    break;
                }
                i++;
            }
            return i;
        }
        private bool getProperties (T t, ref ArrayList sPropertiesKey, ref ArrayList sPropertiesValue) {
            //sPropertiesKey = new ArrayList();
            //sPropertiesValue = new ArrayList();
            if (t == null) {
                return false;
            }
            System.Reflection.PropertyInfo[] properties = t.GetType ().GetProperties (System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
            if (properties.Length <= 0) {
                return false;
            }
            foreach (System.Reflection.PropertyInfo item in properties) {
                string name = item.Name;
                object value = item.GetValue (t, null);
                var itemType = value?.GetType () ?? typeof (object); //获得属性的类型
                sPropertiesKey.Add (name);
                sPropertiesValue.Add (value);
                /*if(value == null){
                    sPropertiesValue.Add (null);
                }else{
                    sPropertiesValue.Add (value);
                }*/

                /*if (item.PropertyType.IsValueType) {
                    
                } else {
                    getProperties ((T) value, ref sPropertiesKey, ref sPropertiesValue);
                }*/
            }
            return true;
        }
    }
}